'use strict';
const mysql = require("mysql");

let pool = mysql.createPool({
    host: "localhost",
    port: 8889,
    user: "root",
    password: "root",
    database: "vue-mall",
    multipleStatements: true // 是否允许执行多条sql
});
// NodeJS MySql 执行多条sql语句
// 设置 multipleStatements 属性为 true
// https://www.cnblogs.com/hzj680539/p/8032270.html

//将查询数据以数组对象返回
let QUERY_All = (sql, ...params) => {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject("错误信息【连接】: " + err.sqlMessage);
                return;
            }
            connection.query(sql, params, function (error, res) {
                connection.release();
                if (error) {
                    reject("错误信息【查询】: " + error.sqlMessage);
                    return;
                }
                resolve(res);
            });
        });
    });
};

//返回查询数据的第一条
var QUERY_FIRST = (sql, ...params) => {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject("错误信息【连接】: " + err.sqlMessage);
                return;
            }
            // connection.query('select * from table where age=? and tel in (?);', [20, [1, 2, 3]], function () {})
            connection.query(sql, params, function (error, res) {
                connection.release();
                if (error) {
                    reject("错误信息【查询】: " + error.sqlMessage);
                    return;
                }
                resolve(res[0] || null);
            });
        });
    });
};

//返回查询的结果
var QUERY_RESULT = (sql, ...params) => {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject("错误信息【连接】: " + err.sqlMessage);
                return;
            }
            connection.query(sql, params, function (error, res) {
                connection.release();
                if (error) {
                    reject("错误信息【查询】: " + error.sqlMessage);
                    return;
                }
                for (let i in res[0]) {
                    resolve(res[0][i] || null);
                    return;
                }
                resolve(null);
            });
        });
    });
}

// 查询数据，返回res
// db.QUERY_DATA('test', {name: 'GC'})
var QUERY_DATA = (tableName, where={}) => {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject("错误信息【连接】: " + err.sqlMessage);
                return;
            }
            var _WHERE = '';
            for (var k2 in where) {
                if (_WHERE === "") {
                    _WHERE += k2 + "='" + where[k2] + "'";
                } else {
                    _WHERE += " AND " + k2 + "='" + where[k2] + "'";
                }
            }
            var sql = `SELECT * FROM ${tableName} WHERE ${_WHERE === '' ? 1 : _WHERE}`;
            console.log(sql);
            connection.query(sql, function (error, res) {
                connection.release();
                if (error) {
                    reject("错误信息【查询】: " + error.sqlMessage);
                    return;
                }
                resolve(res);
            });
        });
    });
}

// 插入一条数据，返回res
// db.INSERT_DATA('test', {name: 'Sherry'})
var INSERT_DATA = (tableName, datas) => {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject("错误信息【连接】: " + err.sqlMessage);
                return;
            }
            var fields = '';
            var values = '';
            for (var k in datas) {
                fields += k + ',';
                values = values + "'" + datas[k] + "',"
            }
            fields = fields.slice(0, -1);
            values = values.slice(0, -1);
            var sql = `INSERT INTO ${tableName} (${fields}) VALUES (${values})`;
            console.log(sql);
            connection.query(sql, function (error, res) {
                connection.release();
                if (error) {
                    reject("错误信息【查询】: " + error.sqlMessage);
                    return;
                }

                resolve(res);
            });
        });
    });
}

// 更新一条数据，返回res
// db.UPDATE_DATA('test', {name: 'GC'}, {name: 'GuCheng'})
var UPDATE_DATA = (tableName, sets, where) => {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject("错误信息【连接】: " + err.sqlMessage);
                return;
            }
            var _SETS = '';
            var _WHERE = '';
            for (var k in sets) {
                _SETS += k + "='" + sets[k] + "',";
            }
            _SETS = _SETS.slice(0, -1);
            for (var k2 in where) {
                if (_WHERE === "") {
                    _WHERE += k2 + "='" + where[k2] + "'";
                } else {
                    _WHERE += " AND " + k2 + "='" + where[k2] + "'";
                }
            }
            var sql = `UPDATE ${tableName} SET ${_SETS} WHERE ${_WHERE === '' ? 1 : _WHERE}`;
            console.log(sql);
            connection.query(sql, function (error, res) {
                connection.release();
                if (error) {
                    reject("错误信息【查询】: " + error.sqlMessage);
                    return;
                }
                resolve(res);
            });
        });
    });
}

// 删除一条数据，返回res
// db.DELETE_DATA('test', {name: 'Sherry'})
var DELETE_DATA = (tableName, where) => {
    return new Promise(function (resolve, reject) {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject("错误信息【连接】: " + err.sqlMessage);
                return;
            }
            var _WHERE = '';
            for (var k2 in where) {
                if (_WHERE === "") {
                    _WHERE += k2 + "='" + where[k2] + "'";
                } else {
                    _WHERE += " AND " + k2 + "='" + where[k2] + "'";
                }
            }
            var sql = `DELETE FROM ${tableName} WHERE ${_WHERE === '' ? 1 : _WHERE}`;
            console.log(sql);
            connection.query(sql, function (error, res) {
                connection.release();
                if (error) {
                    reject("错误信息【查询】: " + error.sqlMessage);
                    return;
                }
                resolve(res);
            });
        });
    });
}

//模块导出
module.exports = {
    QUERY_All,
    QUERY_FIRST,
    QUERY_RESULT,
    QUERY_DATA,
    INSERT_DATA,
    UPDATE_DATA,
    DELETE_DATA,
}

// const mysql = require('./mysql.js');

// (async () => {
//     let s = await mysql.row(sql, params);
//     console.log(s);
// })();


// function query(sql, callback) {
//     pool.getConnection(function (err, connection) {
//         connection.query(sql, function (err, rows) {
//             callback(err, rows);
//             connection.release();
//         });
//     });
// }

// exports.query = query;

// 建表：参考[Node+Koa2+Mysql 搭建简易博客](https://blog.csdn.net/wclimb/article/details/77890793)
// let users = `create table if not exists users(
//     id INT NOT NULL AUTO_INCREMENT,
//     name VARCHAR(100) NOT NULL,
//     pass VARCHAR(100) NOT NULL,
//     avator VARCHAR(100) NOT NULL,
//     moment VARCHAR(100) NOT NULL,
//     PRIMARY KEY ( id )
//     );`